#!/usr/bin/env node

const mysql = require('mysql2/promise'),
      log   = console.log,
      Koa   = require('koa'),
      app   = new Koa(),
      Router = require('koa-router'),
      router = new Router(),
      fs    = require('fs'),
      path  = require('path'),
      cors  = require('koa2-cors'),
      http  = require('http'),
      https = require('https');

let con = mysql.createPool({
    host:'154.8.156.240',
    port:'2020',
    user:'root',
    password:'Ljj0222.',
    database:'tuijian'
});
//商家入驻
async function updateshop(id,shop_name, shop_addr, shop_jwd, shop_tel, shop_time, num, score, img1, img2, img3, state){
  const sql = `update Merchants_settled_table set shop_name = '${shop_name}', shop_addr = '${shop_addr}', shop_jwd = '${shop_jwd}', shop_tel = '${shop_tel}', shop_time = '${shop_time}',num = '${num}',score = '${score}', img1 = '${img1}', img2 = '${img2}', img3 = '${img3}' ,state = '${state}' where id  = ${id}`;  
  let [rows] = await con.execute(sql);
  return rows;
}
//商家注册
async function insertzh(open_id,shop_username,shop_password){
  const sql = `insert into Merchants_settled_table(open_id,shop_username,shop_password) values('${open_id}','${shop_username}','${shop_password}')`;
  let [rows] = await con.execute(sql);
  return rows;
}
//是否已经注册
async function get_openid(open_id){
  const sql = `select * from Merchants_settled_table where open_id = '${open_id}'`;
  let [rows] = await con.execute(sql);
  return rows;
}
//查询是否注册
async function get_zhuce(open_id,shop_username,shop_password){
  const sql = `select * from Merchants_settled_table where open_id = '${open_id}' and shop_username ='${shop_username}' and shop_password ='${shop_password}'`; 
  let [rows] = await con.execute(sql);
  return rows;
}
//更改商家状态
async function select_page(pagesize,pagenum){
  const sql = `select * from Merchants_settled_table where state = '1' limit ${pagesize},${pagenum}`;
  log(sql)
  let [rows] = await con.execute(sql);
  return rows;
}
async function getstate(state){
  const sql = `select * from Merchants_settled_table where state = '${state}'`;
  let [rows] = await con.execute(sql);
  return rows;
}
//查询总数
async function getnum(){
  const sql =`SELECT COUNT(*) as count from Merchants_settled_table where state = '1'`;
  let [rows] = await con.execute(sql);
  return rows;
}
//更改密码
async function updatepassword(id, shop_password){
  const sql = `update Merchants_settled_table set shop_password = '${shop_password}' where id = ${id}`;
  let [rows] = await con.execute(sql);
  return rows;
}
//检查账号是否已经注册过了
async function get_account(shop_username){
  const sql = `select * from Merchants_settled_table where shop_username = '${shop_username}'`;
  let [rows] = await con.execute(sql);
  return rows;
}
//通过id查询
async function get_id(id){
  const sql = `select * from Merchants_settled_table where id = ${id}`;
  let [rows] = await con.execute(sql);
  return rows;
}
//更改state状态
async function update_state(id, state){
  const sql = `update Merchants_settled_table set state = '${state}' where id = ${id}`;
  let [rows] = await con.execute(sql);
  return rows;
}
(async function(){
    //log(await getAreas());
})();

module.exports={
  get_id,
  update_state,
  updateshop,
  get_zhuce,
  get_openid,
  insertzh,
  getstate,
  updatepassword,
  get_account,
  getnum,
  select_page,
}

